﻿using Dapper;
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using WebApplication1.Entity;
using yunbo.Model;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.SSMRepositorys
{
    public class UserRepositorys
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public User Select(string name, string pwd)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[User] where user_uname='{name}'and user_pwd='{pwd}' and user_state=1 and is_deleted=1";
                User user = connection.Query<User>(sql).FirstOrDefault();
                return user;
            }
        }
        public List<User> GetUser()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[User]";
                List<User> user = connection.Query<User>(sql).ToList();
                return user;
            }
        }
        public int PostUser(User user)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[User] ([user_uname],[user_pwd],[user_job_code],[user_name],[user_phone],[user_email],[user_state],[user_create_by],[user_create_time],[is_deleted])\r\nvalues('{user.user_uname}','{user.user_pwd}','{user.user_job_code}','{user.user_name}','{user.user_phone}','{user.user_email}',{user.user_state},{user.user_create_by},'{user.user_create_time}',1)";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int DeleteUser(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[User] set is_deleted=0 WHERE user_id={id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYUser GetFYUser(int PageNumber, int PageSize)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[User] where is_deleted=1 \r\nORDER BY  [user_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<User> user = connection.Query<User>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[User] where is_deleted=1";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYUser fYUser = new FYUser()
                {
                    User = user,
                    zon = zon,
                };
                return fYUser;
            }
        }
        public int UpdateUser(User user)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[User] set [user_uname]='{user.user_uname}',[user_pwd]='{user.user_pwd}',[user_name]='{user.user_name}',[user_phone]='{user.user_phone}',[user_email]='{user.user_email}',[user_update_by]={user.user_update_by},[user_update_time]='{user.user_update_time}' where user_id={user.user_id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYUser GetFYMHUser(int PageNumber, int PageSize, int? state, string? uname)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[User] WHERE user_state like '%{state}%' and user_uname like '%{uname}%' and is_deleted=1\r\nORDER BY  [user_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<User> user = connection.Query<User>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[User] WHERE user_state like '%{state}%' and user_uname like '%{uname}%' and is_deleted=1";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYUser fYUser = new FYUser()
                {
                    User = user,
                    zon = zon,
                };
                return fYUser;
            }
        }
        public int UpdateUserZT(string code, int state)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[User] set user_state={state} where user_job_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
